﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
namespace DAO
{
    public class DataProvider
    {
        
         static readonly  string _cnStr = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=KhachSan.mdb";
         static public OleDbConnection ConectDatabase()
         {
             OleDbConnection cn = new OleDbConnection(_cnStr);
             cn.Open();
             return cn;
         }
        static public int intExcuteQueRy(string Olde)
        {
            int r = -1;
            OleDbConnection cnn = new OleDbConnection(_cnStr);
            OleDbCommand cmd = new OleDbCommand(Olde, cnn);
            cnn.Open();
            r = cmd.ExecuteNonQuery();
            cnn.Close();
            return r;
        }
        public static DataTable LoadTable(string sql)
        {
            OleDbConnection cn = new OleDbConnection(_cnStr);
            OleDbDataAdapter da = new OleDbDataAdapter(sql, cn);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public DataSet GetDataSet(string sql)
        {
            OleDbConnection cn = new OleDbConnection(_cnStr);
            OleDbDataAdapter da = new OleDbDataAdapter(sql, cn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }
        //// taocomand kieu StoredProdure cho query la produce
        public static OleDbCommand ExecProduce(string nameproduce, OleDbConnection obcn )
        {
            OleDbCommand cmd = new OleDbCommand (nameproduce,obcn);
            cmd.CommandType = CommandType.StoredProcedure;
            return cmd;
        }
        // taocomand kieu text cho query la produce
        public static OleDbCommand ExecutetextQuery(string nameproduce, OleDbConnection obcn)
        {
            OleDbCommand cmd = new OleDbCommand(nameproduce, obcn);
            cmd.CommandType = CommandType.Text;
            return cmd;
        }

        // tra ve kieu int doi voi query co tham so
        public static int ExecQueryWithParameters(string sql, params Object[] parameters)
        {
            OleDbConnection cn = new OleDbConnection(_cnStr);
             OleDbCommand command = new OleDbCommand(sql, cn);
            command.CommandType = CommandType.StoredProcedure;

            for (int i = 0; i < parameters.Length; i += 2)
            {    

                command.Parameters.AddWithValue(parameters[i].ToString(), parameters[i + 1]);
            }

            command.Connection.Open();
            int rows = command.ExecuteNonQuery();
            cn.Close();
            return rows;

        }
        // tạo OledbComand có nhìu tham số truyền vào với câu query
        public static OleDbCommand CreateCommand(String sql, params Object[] parameters)
        {
            OleDbConnection cn = new OleDbConnection(_cnStr);
            OleDbCommand command = new OleDbCommand(sql, cn);
            command.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < parameters.Length; i += 2)
            {
                command.Parameters.AddWithValue(parameters[i].ToString(), parameters[i + 1]);
            }
            return command;
        }
        // trả về databale Với câu câu Query có tham số (strore produce)
         public static DataTable DataTableQueryParameters(string sql, params Object[] parameters)
        {
            OleDbConnection cn = new OleDbConnection(_cnStr);
            OleDbCommand command = DataProvider.CreateCommand(sql, parameters);
            command.Connection.Open();
            command.ExecuteNonQuery();
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = command;
            DataTable dt = new DataTable();
            da.Fill(dt);
            cn.Close();
            return dt;
            

        }
         public static int getMAX(string query)
         {
             int id = -1;
             OleDbConnection cnn = new OleDbConnection(_cnStr);

             OleDbCommand cmd = new OleDbCommand(query, cnn);
             cmd.Connection.Open();
             id = (int)cmd.ExecuteScalar();
             cmd.Connection.Close();
             return id;

         }

    }
}
